﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using University.Workshop.Model;
using System.Web;
using System.Globalization;
using System.Collections.ObjectModel;
using System.Data;

namespace University.Workshop.DataAccess
{
    public static class UserDataAccess
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static User InsertUser(User user)
        {
            if (user != null)
            {
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(ConnString);
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO [USER](Status, Password, FirstName, LastName, Gender, Birthday, Email, Phone, MobilePhone, Department, City)" +
                                       " VALUES(@status, @passwordHash, @firstName, @lastName, @gender, @birthday, @email, @phone, @mobilephone, @department, @city)";
                    cmd.Parameters.AddWithValue("status", user.Status);
                    cmd.Parameters.AddWithValue("passwordHash", user.Password.GetHashCode().ToString(CultureInfo.CurrentCulture));
                    cmd.Parameters.AddWithValue("firstName", user.PersonalInformation.FirstName);
                    cmd.Parameters.AddWithValue("lastName", user.PersonalInformation.LastName);
                    cmd.Parameters.AddWithValue("gender", user.PersonalInformation.Gender);
                    cmd.Parameters.AddWithValue("birthday", user.PersonalInformation.Birthday);
                    cmd.Parameters.AddWithValue("email", user.ContactInformation.Email);
                    cmd.Parameters.AddWithValue("phone", user.ContactInformation.Phone);
                    cmd.Parameters.AddWithValue("mobilePhone", user.ContactInformation.MobilePhone);
                    cmd.Parameters.AddWithValue("department", user.ContactInformation.Department);
                    cmd.Parameters.AddWithValue("city", user.ContactInformation.City);
                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting an user");
                    }
                    user.Id = Convert.ToInt64(cmdId.ExecuteScalar(), CultureInfo.CurrentCulture);
                    return user;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error inserting an user", ex);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }
        public static User UserExists(string email, string password)
        {
            
            SqlConnection co = null;
            try
            {
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM [USER] Where Email=@Email and Password=@Password";
                cmd.Parameters.AddWithValue("Email", email);
                cmd.Parameters.AddWithValue("Password", password);
                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                User user = new User();
                while (reader.Read())
                {
                    user.ContactInformation.Email = email;
                    user.Password = password;
                    user.PersonalInformation.FirstName = reader["FirstName"].ToString();
                    
                }
                return user;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting user", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static User GetUserByEmail(string email)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [USER] Where Email = @email";
                cmd.Parameters.AddWithValue("email", email);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                User user = new User();

                while (reader.Read())
                {
                    user.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    bool inactive = bool.Parse(reader["Status"].ToString());
                    if (inactive)
                        user.Status = AccountState.Inactive;
                    else user.Status = AccountState.Active;
                    user.PersonalInformation.FirstName = reader["FirstName"].ToString();
                    user.PersonalInformation.LastName = reader["LastName"].ToString();
                    user.PersonalInformation.Gender = reader["Gender"].ToString()[0];
                    user.PersonalInformation.Birthday = (DateTime)reader["Birthday"];
                    user.ContactInformation.Email = reader["Email"].ToString();
                    user.ContactInformation.Phone = int.Parse(reader["Phone"].ToString(), CultureInfo.CurrentCulture);
                    user.ContactInformation.MobilePhone = int.Parse(reader["MobilePhone"].ToString(), CultureInfo.CurrentCulture);
                    user.ContactInformation.Department = reader["Department"].ToString();
                    user.ContactInformation.City = reader["City"].ToString();
                    user.Password = reader["Password"].ToString();
                }
                return user;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting an user", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static User GetUserById(long id)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [USER] Where Id = @id";
                cmd.Parameters.AddWithValue("id", id);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                User user = new User();

                while (reader.Read())
                {
                    user.Id = Int64.Parse(reader["Id"].ToString(), CultureInfo.CurrentCulture);
                    bool inactive = bool.Parse(reader["Status"].ToString());
                    if (inactive)
                        user.Status = AccountState.Inactive;
                    else user.Status = AccountState.Active;
                    user.PersonalInformation.FirstName = reader["FirstName"].ToString();
                    user.PersonalInformation.LastName = reader["LastName"].ToString();
                    user.PersonalInformation.Gender = reader["Gender"].ToString()[0];
                    user.PersonalInformation.Birthday = (DateTime)reader["Birthday"];
                    user.ContactInformation.Email = reader["Email"].ToString();
                    user.ContactInformation.Phone = int.Parse(reader["Phone"].ToString(), CultureInfo.CurrentCulture);
                    user.ContactInformation.MobilePhone = int.Parse(reader["MobilePhone"].ToString(), CultureInfo.CurrentCulture);
                    user.ContactInformation.Department = reader["Department"].ToString();
                    user.ContactInformation.City = reader["City"].ToString();
                }
                return user;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting an user", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static void UpdateUserInformation(User user)
        {
            if (user != null)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnString);
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "UPDATE [USER] SET Phone=@phone, MobilePhone=@mobilePhone, Department=@department, City=@city WHERE Email=@email";
                    cmd.Parameters.AddWithValue("phone", user.ContactInformation.Phone);
                    cmd.Parameters.AddWithValue("mobilePhone", user.ContactInformation.MobilePhone);
                    cmd.Parameters.AddWithValue("department", user.ContactInformation.Department);
                    cmd.Parameters.AddWithValue("city", user.ContactInformation.City);
                    cmd.Parameters.AddWithValue("email", user.ContactInformation.Email);
                    connection.Open();
                    int resp = cmd.ExecuteNonQuery();
                    if (resp <= 0)
                    {
                        throw new DataAccessException("Error update information");
                    }
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error update information user", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
        }

        public static void ChangeUserPassword(User anUser, string newPass)
        {
            if (anUser != null && newPass != null)
            {
                SqlConnection con = null;
                try
                {
                    con = new SqlConnection(ConnString);
                    SqlCommand comd = con.CreateCommand();
                    comd.CommandText = "UPDATE [USER] SET Password=@password WHERE Email=@email ";
                    comd.Parameters.AddWithValue("Password", newPass.GetHashCode().ToString(CultureInfo.CurrentCulture));
                    comd.Parameters.AddWithValue("Email", anUser.ContactInformation.Email);
                    con.Open();
                    int resp = comd.ExecuteNonQuery();
                    if (resp <= 0)
                    {
                        throw new DataAccessException("Error changing User Password");
                    }
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error changing User Password", ex);
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            else
                throw new DataAccessException("Invalid user");

        }

        public static void CancelUserAccount(User anUser,AccountState state)
        {
            if (anUser != null)
            {
                SqlConnection con = null;
                try
                {
                    con = new SqlConnection(ConnString);
                    SqlCommand comd = con.CreateCommand();
                    comd.CommandText = "UPDATE [USER] SET Status = @status WHERE Email=@email  ";
                    comd.Parameters.AddWithValue("Status", state);
                    comd.Parameters.AddWithValue("Email", anUser.ContactInformation.Email);
                    con.Open();
                    int resp = comd.ExecuteNonQuery();
                    if (resp <= 0)
                    {
                        throw new DataAccessException("Error Cancelling User Account");
                    }
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error Cancelling User Account", ex);
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            else
                throw new DataAccessException("Invalid user");

        }
        public static void ReactiveUserAccount(User anUser, AccountState state)
        {
            if (anUser != null)
            {
                SqlConnection con = null;
                try
                {
                    con = new SqlConnection(ConnString);
                    SqlCommand comd = con.CreateCommand();
                    comd.CommandText = "UPDATE [USER] SET Status = @status WHERE Email=@email  ";
                    comd.Parameters.AddWithValue("Status", state);
                    comd.Parameters.AddWithValue("Email", anUser.ContactInformation.Email);
                    con.Open();
                    int resp = comd.ExecuteNonQuery();
                    if (resp <= 0)
                    {
                        throw new DataAccessException("Error reactiving User Account");
                    }
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error reactiving User Account", ex);
                }
                finally
                {
                    if (con != null)
                    {
                        con.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            else
                throw new DataAccessException("Invalid user Reactivate Account");

        }

        public static object GetUserByName(string firstName)
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            try
            {
                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT Id,FirstName,LastName FROM [USER] where FirstName=@firstName and Status!=@status";
                command.Parameters.AddWithValue("FirstName",firstName);
                command.Parameters.AddWithValue("Status",AccountState.Inactive);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "[USER]");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["[USER]"].DefaultView;
                return dataView;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting users by name", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }


    }
}
